﻿/* 获取指定表名的外键引用 */
SELECT a.OWNER,a.TABLE_NAME,b.COLUMN_NAME,c.OWNER,c.TABLE_NAME,e.COLUMN_NAME
FROM ALL_CONSTRAINTS a 
JOIN USER_CONS_COLUMNS b ON a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
JOIN ALL_CONSTRAINTS c ON a.R_CONSTRAINT_NAME=c.CONSTRAINT_NAME
JOIN USER_CONS_COLUMNS e ON e.CONSTRAINT_NAME=c.CONSTRAINT_NAME
WHERE a.TABLE_NAME='DEMO_USERROLES' AND a.CONSTRAINT_TYPE='R';
/* 该空间下所有用户对应的表集合 */
SELECT * FROM USER_ALL_TABLES;
/* 当前用户下所有表 */
SELECT * FROM USER_TABLES;


/* 获取指定表的 DDL 文本 */
SELECT DBMS_METADATA.GET_DDL('TABLE','DEMO_USERROLES') FROM DUAL;

/* 获取表的列信息 */
SELECT a.COLUMN_NAME 字段名,a.DATA_TYPE 数据类型,a.DATA_LENGTH 长度
,a.DATA_PRECISION 整数位
,a.DATA_SCALE 小数位
,a.NULLABLE 允许空值
,a.DATA_DEFAULT 缺省值
,b.COMMENTS 备注
FROM  USER_TAB_COLUMNS a,USER_COL_COMMENTS b WHERE a.COLUMN_NAME=b.COLUMN_NAME 
AND    a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_NAME='SHARE_USER';


/* 主键和外键的创建方式 */
CREATE TABLE DEMO_USER
(
       ID NUMBER NOT NULL PRIMARY KEY,
       USERNAME VARCHAR(50) NOT NULL UNIQUE,
       PASSWORD VARCHAR(500) NOT NULL,
       FULLNAME NVARCHAR2(50) NOT NULL,
       MEMO NVARCHAR2(2000) NOT NULL
);

CREATE TABLE DEMO_ROLE
(
       ID NUMBER NOT NULL PRIMARY KEY,
       ROLENAME NVARCHAR2(500) NOT NULL
);

CREATE TABLE DEMO_USERROLES
(
       USERID NUMBER NOT NULL REFERENCES DEMO_USER(ID),
       ROLEID NUMBER NOT NULL REFERENCES DEMO_ROLE(ID),
       PRIMARY KEY(USERID,ROLEID)
);